Imports System.Data.SqlClient
Imports System.Data

Public Class <OwnerU>_<TableU>DataClass

    Public Function Select_Records(ByVal <DRField> As <DRType>) As DataTable
        Dim connection As SqlConnection = <Database>DataClass.GetConnection
        Dim selectProcedure As String = "[<Owner>].[<TableU>Select_Records]"
        Dim selectCommand As New SqlCommand(selectProcedure, connection)
        selectCommand.CommandType = CommandType.StoredProcedure
        selectCommand.Parameters.AddWithValue("@<DRField>", <DRField>)
        Dim dt As New DataTable
        Try
            connection.Open()
            Dim reader As SqlDataReader = selectCommand.ExecuteReader()
            If reader.HasRows Then
                dt.Load(reader)
            End If
            reader.Close()
        Catch ex As SqlException
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")
        Finally
            connection.Close()
        End Try
        Return dt
    End Function

    Public Function Search(Byval <DRField> AS <DRType>, ByVal sField As String, ByVal sCondition As String, ByVal sValue As String) As DataTable
        Dim connection As SqlConnection = <Database>DataClass.GetConnection
        Dim selectProcedure As String = "[<Owner>].[<TableU>Search]"
        Dim selectCommand As New SqlCommand(selectProcedure, connection)
        selectCommand.CommandType = CommandType.StoredProcedure
    <If Loop>
   	<IsNotText>
   	<And>
   	<IsNotNText>
        <And>
        <IsRelated>
	       <And>
	       <IsMiscTag>
        selectCommand.Parameters.AddWithValue("@<RDisplayField><MiscTag>", IIf(sField = "<FieldC>", sValue, DBNull.Value))
   	<IsNotText>
   	<And>
   	<IsNotNText>
        <And>
        <IsRelated>
	       <And>
	       <IsNotMiscTag>
        selectCommand.Parameters.AddWithValue("@<RDisplayField>", IIf(sField = "<FieldC>", sValue, DBNull.Value))
   	<IsNotText>
   	<And>
   	<IsNotNText>
        <And>
        <IsNotRelated>
        selectCommand.Parameters.AddWithValue("@<FieldU>", IIf(sField = "<FieldC>", sValue, DBNull.Value))
    </If Loop>
        selectCommand.Parameters.AddWithValue("@SearchCondition", sCondition)
        selectCommand.Parameters.AddWithValue("@R<DRField>", <DRField>)
        Dim dt As New DataTable
        Try
            connection.Open()
            Dim reader As SqlDataReader = selectCommand.ExecuteReader()
            If reader.HasRows Then
                dt.Load(reader)
            End If
            reader.Close()
        Catch ex As SqlException
            Return dt
        Finally
            connection.Close()
        End Try
        Return dt
    End Function

    Public Function Select_Record(ByVal cls<OwnerU>_<TableU>Para As <OwnerU>_<TableU>Class) As <OwnerU>_<TableU>Class
        Dim cls<OwnerU>_<TableU> As New <OwnerU>_<TableU>Class
        Dim connection As SqlConnection = <Database>DataClass.GetConnection
        Dim selectProcedure As String = "[<Owner>].[<TableU>Select]"
        Dim selectCommand As New SqlCommand(selectProcedure, connection)
        selectCommand.CommandType = CommandType.StoredProcedure
        <If Loop>
           <IsPKey>
        selectCommand.Parameters.AddWithValue("@<FieldU>", cls<OwnerU>_<TableU>Para.<FieldU>)
        </If Loop>
        <If Loop>
           <IsNotPKey>
           <And>
           <IsIdentity>
        selectCommand.Parameters.AddWithValue("@<FieldU>", cls<OwnerU>_<TableU>Para.<FieldU>)
        </If Loop>
        <If Loop>
           <IsNotIdentityCount>
           <And>
           <IsNotPKeyCount>
   	   <And>
           <IsNotText>
           <And>
           <IsNotNText>
           <And>
           <IsNotXML>
        selectCommand.Parameters.AddWithValue("@<FieldU>", cls<OwnerU>_<TableU>Para.<FieldU>)
           <ExitLoop>
        </If Loop>
        Try
            connection.Open()
            Dim reader As SqlDataReader = selectCommand.ExecuteReader(CommandBehavior.SingleRow)
            If reader.Read Then
                With cls<OwnerU>_<TableU>
                <If Loop>
                    <IsNull>
                    <And>
                    <IsNullable>
                    <And>
                    <IsFloat>
                    .<FieldU> = If(IsDBNull(reader("<FieldU>")), Nothing, CType(CType(reader("<FieldU>"), Double?), Decimal?))
                    <IsNull>
                    <And>
                    <IsNullable>
                    <And>
                    <IsReal>
                    .<FieldU> = If(IsDBNull(reader("<FieldU>")), Nothing, CType(CType(reader("<FieldU>"), Single?), Decimal?))
                    <IsNull>
                    <And>
                    <IsNullable>
                    <And>
                    <IsNotFloat>
                    <And>
                    <IsNotReal>
                    .<FieldU> = If(IsDBNull(reader("<Field>")), Nothing, CType(reader("<Field>"), <VBType>?))
                    <IsNull>
                    <And>
                    <IsNotNullable>
                    .<FieldU> = If(IsDBNull(reader("<Field>")), Nothing, reader("<Field>").ToString)
                    <IsNotNull>
                    .<FieldU> = <Convert>(reader("<Field>"))
                </If Loop>
                End With
            Else
                cls<OwnerU>_<TableU> = Nothing
            End If
            reader.Close()
        Catch ex As SqlException
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")
        Finally
            connection.Close()
        End Try
        Return cls<OwnerU>_<TableU>
    End Function

    Public Function Add(ByVal cls<OwnerU>_<TableU> As <OwnerU>_<TableU>Class) As Boolean
        Dim connection As SqlConnection = <Database>DataClass.GetConnection
        Dim insertProcedure As String = "[<Owner>].[<TableU>Insert]"
        Dim insertCommand As New SqlCommand(insertProcedure, connection)
        insertCommand.CommandType = CommandType.StoredProcedure
        <If Loop>
           <IsNotIdentity>
           <And>
           <IsNull>
           <And>
           <IsNullable>
        insertCommand.Parameters.AddWithValue("@<FieldU>", IIf(cls<OwnerU>_<TableU>.<FieldU>.HasValue, cls<OwnerU>_<TableU>.<FieldU>, DBNull.Value))
           <IsNotIdentity>
           <And>
           <IsNull>
           <And>
           <IsNotNullable>
        insertCommand.Parameters.AddWithValue("@<FieldU>", IIf(Not IsNothing(cls<OwnerU>_<TableU>.<FieldU>), cls<OwnerU>_<TableU>.<FieldU>, DBNull.Value))
           <IsNotIdentity>
           <And>
           <IsNotNull>
        insertCommand.Parameters.AddWithValue("@<FieldU>", cls<OwnerU>_<TableU>.<FieldU>)
        </If Loop>
        insertCommand.Parameters.Add("@ReturnValue", System.Data.SqlDbType.Int)
        insertCommand.Parameters("@ReturnValue").Direction = ParameterDirection.Output
        Try
            connection.Open()
            insertCommand.ExecuteNonQuery()
            Dim count As Integer = System.Convert.ToInt32(insertCommand.Parameters("@ReturnValue").Value)
            If count > 0 Then
                Return True
            Else
                Return False
            End If
        Catch ex As SqlException
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")
        Finally
            connection.Close()
        End Try
        Return True
    End Function

    Public Function Update(ByVal cls<OwnerU>_<TableU>Old As <OwnerU>_<TableU>Class, _
           ByVal cls<OwnerU>_<TableU>New As <OwnerU>_<TableU>Class) As Boolean
        Dim connection As SqlConnection = <Database>DataClass.GetConnection
        Dim updateProcedure As String = "[<Owner>].[<TableU>Update]"
        Dim updateCommand As New SqlCommand(updateProcedure, connection)
        updateCommand.CommandType = CommandType.StoredProcedure
        <If Loop>
           <IsNotIdentity>
           <And>
           <IsNull>
           <And>
           <IsNullable>
        updateCommand.Parameters.AddWithValue("@New<FieldU>", IIf(cls<OwnerU>_<TableU>New.<FieldU>.HasValue, cls<OwnerU>_<TableU>New.<FieldU>, DBNull.Value))
           <IsNotIdentity>
           <And>
           <IsNull>
           <And>
           <IsNotNullable>
        updateCommand.Parameters.AddWithValue("@New<FieldU>", IIf(Not IsNothing(cls<OwnerU>_<TableU>New.<FieldU>), cls<OwnerU>_<TableU>New.<FieldU>, DBNull.Value))
           <IsNotIdentity>
           <And>
           <IsNotNull>
        updateCommand.Parameters.AddWithValue("@New<FieldU>", cls<OwnerU>_<TableU>New.<FieldU>)
        </If Loop>
        <If Loop>
           <IsNotText>
           <And>
           <IsNotNText>
           <And>
           <IsNotXML>
           <And>
           <IsNotNull>
        updateCommand.Parameters.AddWithValue("@Old<FieldU>", cls<OwnerU>_<TableU>Old.<FieldU>)
           <IsNotText>
           <And>
           <IsNotNText>
           <And>
           <IsNotXML>
           <And>
           <IsNull>
           <And>
           <IsNullable>
        updateCommand.Parameters.AddWithValue("@Old<FieldU>", IIf(cls<OwnerU>_<TableU>Old.<FieldU>.HasValue, cls<OwnerU>_<TableU>Old.<FieldU>, DBNull.Value))
           <IsNotText>
           <And>
           <IsNotNText>
           <And>
           <IsNotXML>
           <And>
           <IsNull>
           <And>
           <IsNotNullable>
        updateCommand.Parameters.AddWithValue("@Old<FieldU>", IIf(Not IsNothing(cls<OwnerU>_<TableU>Old.<FieldU>), cls<OwnerU>_<TableU>Old.<FieldU>, DBNull.Value))
        </If Loop>
        updateCommand.Parameters.Add("@ReturnValue", System.Data.SqlDbType.Int)
        updateCommand.Parameters("@ReturnValue").Direction = ParameterDirection.Output
        Try
            connection.Open()
            updateCommand.ExecuteNonQuery()
            Dim count As Integer = System.Convert.ToInt32(updateCommand.Parameters("@ReturnValue").Value)
            If count > 0 Then
                Return True
            Else
                Return False
            End If
        Catch ex As SqlException
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")
        Finally
            connection.Close()
        End Try
        Return True
    End Function

    Public Function Delete(ByVal cls<OwnerU>_<TableU> As <OwnerU>_<TableU>Class) As Boolean
        Dim connection As SqlConnection = <Database>DataClass.GetConnection
        Dim deleteProcedure As String = "[<Owner>].[<TableU>Delete]"
        Dim deleteCommand As New SqlCommand(deleteProcedure, connection)
        deleteCommand.CommandType = CommandType.StoredProcedure
        <If Loop>
           <IsNotText>
           <And>
           <IsNotNText>
           <And>
           <IsNotXML>
           <And>
           <IsNotNull>
        deleteCommand.Parameters.AddWithValue("@Old<FieldU>", cls<OwnerU>_<TableU>.<FieldU>)
           <IsNotText>
           <And>
           <IsNotNText>
           <And>
           <IsNotXML>
           <And>
           <IsNull>
           <And>
           <IsNullable>
        deleteCommand.Parameters.AddWithValue("@Old<FieldU>", IIf(cls<OwnerU>_<TableU>.<FieldU>.HasValue, cls<OwnerU>_<TableU>.<FieldU>, DBNull.Value))
           <IsNotText>
           <And>
           <IsNotNText>
           <And>
           <IsNotXML>
           <And>
           <IsNull>
           <And>
           <IsNotNullable>
        deleteCommand.Parameters.AddWithValue("@Old<FieldU>", IIf(Not IsNothing(cls<OwnerU>_<TableU>.<FieldU>), cls<OwnerU>_<TableU>.<FieldU>, DBNull.Value))
        </If Loop>
        deleteCommand.Parameters.Add("@ReturnValue", System.Data.SqlDbType.Int)
        deleteCommand.Parameters("@ReturnValue").Direction = ParameterDirection.Output
        Try
            connection.Open()
            deleteCommand.ExecuteNonQuery()
            Dim count As Integer = System.Convert.ToInt32(deleteCommand.Parameters("@ReturnValue").Value)
            If count > 0 Then
                Return True
            Else
                Return False
            End If
        Catch ex As SqlException
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")
        Finally
            connection.Close()
        End Try
        Return True
    End Function

    Public Function Delete(ByVal <DRField> As <DRType>) As Boolean
        Dim connection As SqlConnection = <Database>DataClass.GetConnection
        Dim deleteProcedure As String = "[<Owner>].[<TableU>Delete_Records]"
        Dim deleteCommand As New SqlCommand(deleteProcedure, connection)
        deleteCommand.CommandType = CommandType.StoredProcedure
        deleteCommand.Parameters.AddWithValue("@<DRField>", <DRField>)
        deleteCommand.Parameters.Add("@ReturnValue", System.Data.SqlDbType.Int)
        deleteCommand.Parameters("@ReturnValue").Direction = ParameterDirection.Output
        Try
            connection.Open()
            deleteCommand.ExecuteNonQuery()
            Dim count As Integer = System.Convert.ToInt32(deleteCommand.Parameters("@ReturnValue").Value)
            If count > 0 Then
                Return True
            Else
                Return False
            End If
        Catch ex As SqlException
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")
        Finally
            connection.Close()
        End Try
        Return True
    End Function

End Class
